package AccessControl

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/denisenkom/go-mssqldb" // MSSQL 驱动
	"github.com/xuri/excelize/v2"
)

func GetAccessControl() {
	// 数据库连接信息
	connString := "sqlserver://oa:6gNDEfHdLUcj8XlgQKFy@192.168.1.30:1433?database=zk_huadao"
	db, err := sql.Open("mssql", connString)
	if err != nil {
		log.Fatal("Error opening database connection:", err)
	}
	defer db.Close()

	// 执行查询
	rows, err := db.Query("select acc_zone.name as 区域名称,door_name as 门禁名称," +
		"badgenumber as 工号,userinfo.name as 姓名,deptnumber as 部门编号,deptname as 部门名称" +
		"from acc_level_door,acc_level,acc_door,acc_level_emp," +
		"acc_zone,acc_iclockzone," +
		"userinfo,departments" +
		"where acc_level_door.level_id=acc_level.id and acc_door.id=acc_level_door.door_id" +
		"and acc_level.id=acc_level_emp.level_id" +
		"and acc_level_emp.userid_id=userinfo.userid" +
		"and acc_zone.id=acc_iclockzone.zone_id and acc_door.device_id=acc_iclockzone.sn_id" +
		"and userinfo.defaultdeptid=departments.deptid and userinfo.deltag<>1" +
		"order by  acc_zone.name,acc_door.device_id,acc_door.door_no,badgenumber")
	if err != nil {
		log.Fatal("Error querying database:", err)
	}
	defer rows.Close()

	// 创建一个新的 Excel 文件
	f := excelize.NewFile()
	// 创建一个工作表
	index, _ := f.NewSheet("Sheet1")

	// 写入表头
	f.SetCellValue("Sheet1", "A1", "区域名称")
	f.SetCellValue("Sheet1", "B1", "门禁名称")
	f.SetCellValue("Sheet1", "C1", "工号")
	f.SetCellValue("Sheet1", "D1", "姓名")
	f.SetCellValue("Sheet1", "E1", "部门编号")
	f.SetCellValue("Sheet1", "F1", "部门名称")

	// 写入数据
	rowIndex := 2
	for rows.Next() {
		var id int
		var name string
		var age int
		if err := rows.Scan(&id, &name, &age); err != nil {
			log.Fatal("Error scanning row:", err)
		}
		f.SetCellValue("Sheet1", fmt.Sprintf("A%d", rowIndex), id)
		f.SetCellValue("Sheet1", fmt.Sprintf("B%d", rowIndex), name)
		f.SetCellValue("Sheet1", fmt.Sprintf("C%d", rowIndex), age)
		rowIndex++
	}

	// 设置工作表为活动状态
	f.SetActiveSheet(index)

	// 保存 Excel 文件
	if err := f.SaveAs("output.xlsx"); err != nil {
		log.Fatal("Error saving Excel file:", err)
	}

	fmt.Println("数据已成功写入 output.xlsx")
}
